#!/usr/bin/python
# -*- coding: UTF-8 -*- 
import xlrd
import json
import codecs
import os
import types

path = os.getcwd()
print("当前路径 -> %s" %path)
currentPath = os.path.dirname(__file__)
print("当前路径2 -> %s" %currentPath)

exportPath = currentPath + "\\clientJson\\"

filelist = []

for filename in os.listdir(currentPath):
    #目录的路径和文件名拼接起来，得到了文件的绝路路径
    pathStr=os.path.join(currentPath, filename)
    if pathStr.split('.')[-1]=='xlsx':
        filelist.append(pathStr)
#找到当前目录下所有的xlsx表
"""
for root, dirs, files in os.walk(currentPath, topdown=False):
    for name in files:
        str=os.path.join(root, name)
        if str.split('.')[-1]=='xlsx':
            filelist.append(str)
"""

# 存储Excel中的所有数据
excel_data = {}

def run(sheet):
    if sheet.nrows > 0 :
        sheet_data = {}
        # 第一列：id
        ids = sheet.col_values(0)
        ##判断第一列 从第五行开始判断是否有相同的 如果有相同的则为双索引 目前只支持双索引
        ##for firstKeyTmp in range(4, sheet.nrow)


        # 第一行：键
        keys = sheet.row_values(3)
        # 第三行 ： 是否需要导出的表示 C S A ，客户端只用管C 和 A
        exportFlags = sheet.row_values(2)
        # 判断导出行标志是否有C或者A 如果没有这个表就不用导出
        


        # 行长度
        row_len = sheet.row_len(0)
        # 遍历行
        for row_num in range(4, sheet.nrows):
            _id = int(ids[row_num])
            if _id in sheet_data.keys():
                print("11111")
                ##print( type(sheet_data[_id]))
            else:
                sheet_data[_id] = {}
            
            # 遍历列
            for col_num in range(1, row_len):
                if exportFlags[col_num] == 'A' or exportFlags[col_num] == 'C' :
                    key = keys[col_num]
                    value = sheet.row_values(row_num)[col_num]
                    ctype = sheet.cell(row_num, col_num).ctype
                    # 存row_num行col_num列对应的值
                    ##if type(value) is types.StringType:
                        ##sheet_data[_id][key] = value
                       
                    ##else:
                    if ctype == 2 and value % 1 == 0:  # 如果是整形
                        value = int(value)
                    elif ctype == 3:
                        # 转成datetime对象
                        date = datetime(*xldate_as_tuple(value, 0))
                        value = date.strftime('%Y/%d/%m %H:%M:%S')
                    elif ctype == 4:
                        value = True if value == 1 else False
                    sheet_data[_id][key] = value
                
        # 存传入表的所有数据
        excel_data[sheet.name] = sheet_data
        return sheet_data

def deletFiles(path):
    delFiles = os.listdir(path)
    for f in delFiles :
        delFilePath = os.path.join(path, f)
        if os.path.isfile(delFilePath):
            os.remove(delFilePath)
            #print(delFilePath+"removed!")




deletFiles(exportPath)
for index in range(len(filelist)) :
    #print(filelist[index])
    # 读取 Excel
    wk = xlrd.open_workbook(filelist[index])
    # 获取所有工作表名称
    sheet_names = wk.sheet_names()
    # 拿到所有工作表
    sheets = wk.sheets()
     # 遍历表
    #for i in range(len(sheets)):
    sheetData = run(sheets[0])
    if len(sheetData) > 0:
        sheetName = sheets[0].name
         # 将excel_data数据转化成json字符串，缩进为4
        data = json.dumps(sheetData, indent=4,ensure_ascii=False)
        # 将json字符串写入dame.json文件
        with codecs.open(currentPath + "\\clientJson\\"+sheetName + '.json', 'w', 'utf-8') as fir:
            fir.write(data)
        print("导出"+sheetName+"成功")

   
     
